package com.emar.spring.dao;

import java.util.List;

import org.springframework.stereotype.Repository;

import com.emar.spring.common.util.StringUtil;
import com.emar.spring.dto.ParamsDTO;
/**
 * 对记录表操作
 */
@Repository
public class RecodeDao extends BaseDAO{

	
	private String getListSql(ParamsDTO dto, String groupBy, int pageNumber,
			int pageSize) {
		StringBuffer sb = new StringBuffer(
				"SELECT r.`RecordId`, r.`ObjName`, r.`ObjType1`, r.`ObjType2`, r.`isValid`, r.`RecordName`, r.`RecordJudge`, s.`SystemId`, s.`SystemName`, s.`SystemDescribe`, s.`ProtectSortCode`, s.`SecurityLevelCode`, p.`ProjectID`, p.`ProjectName`, p.`ProjectType`, p.`ProjectCode`, p.`ProjectStatus`, p.`ProjectDescribe`, p.`ProjectManagerID`, p.`CommissionCorpName`, p.`CommissionDate`, p.`CommissionCorpLinkName`, p.`CommissionCorpLinkTel`, p.`TargetSystemName`, t.`TestItemCode`, t.`TestItemDescribe`, t.`ProtectSortCode` AS tProtectSortCode, t.`SecurityLevelCode` AS tSecurityLevelCode FROM t_record AS r LEFT JOIN t_system AS s ON r.`SystemId` = s.`SystemId` LEFT JOIN t_project AS p ON s.`ProjectID` = p.`ProjectID` LEFT JOIN t_test AS t ON r.`TestItemCode` = t.`TestItemCode` ");
		sb.append(this.getWhereSql(dto));
		if(StringUtil.isNotEmpty(groupBy)){
			sb.append( " GROUP BY ("+groupBy+")");
		}
		return sb.toString();
	}
	
	/**
	 * 拼接查询结果总数的sql
	 * @param user 
	 * @return
	 */
	private String getCountSql(ParamsDTO pe,String groupBy){
		StringBuffer sb = new StringBuffer();
		sb.append( " SELECT ");
		sb.append( " count(*) as count");
		sb.append( " from t_record AS r LEFT JOIN t_system AS s ON r.`SystemId` = s.`SystemId` LEFT JOIN t_project AS p ON s.`ProjectID` = p.`ProjectID` LEFT JOIN t_test AS t ON r.`TestItemCode` = t.`TestItemCode` ");
		sb.append(this.getWhereSql(pe));
		if(StringUtil.isNotEmpty(groupBy)){
			sb.append( " GROUP BY ("+groupBy+")");
		}
		return sb.toString();
	}
	
	/**
	 * 以单位名称分组，获取总数
	 * @return	返回获取的总数
	 */
	public int getCorpCount(String groupBy){
		String sql= "SELECT COUNT(*) FROM (SELECT COUNT("+groupBy+") t_record AS r LEFT JOIN t_system AS s ON r.`SystemId` = s.`SystemId` LEFT JOIN t_project AS p ON s.`ProjectID` = p.`ProjectID` LEFT JOIN t_test AS t ON r.`TestItemCode` = t.`TestItemCode` GROUP BY ("+groupBy+") ) AS b ";
		return super.getCount(sql);
	}
	
	/**
	 * 获取项目总数
	 * @return
	 */
	public int getRecodeCount(ParamsDTO pe,String groupBy){
		String sql = this.getCountSql(pe,groupBy);
		return super.getCount(sql);
	}
	
	/**
	 * 获取分组数
	 * @return
	 */
	public int getRecodeCountGroup(ParamsDTO pe,String groupBy){
		StringBuffer sb = new StringBuffer();
		sb.append( "SELECT COUNT(*) FROM (");
		sb.append(this.getListSql(pe, groupBy, 0,0));
		sb.append( " ) as t");
		
		return super.getCount(sb.toString());
	}
	
	public List<ParamsDTO> getProjectDTOPageList(ParamsDTO pe ,String groupBy, int pageNumber,int pageSize){
		String sql = this.getListSql(pe,groupBy, pageNumber, pageSize);
		List<ParamsDTO> list = this.getPageList(sql, ParamsDTO.class,pageNumber,pageSize);
		return list;
	}
	
	public List<ParamsDTO> getProjectDTOList(ParamsDTO pe ,String groupBy, int pageNumber,int pageSize){
		String sql = this.getListSql(pe,groupBy, pageNumber, pageSize);
		List<ParamsDTO> list = this.getList(sql,ParamsDTO.class);
		return list;
	}
	
	/**
	 * 获取记录表的数据，进行left连接查询，关联其他的表获取必要字段
	 * @param pe
	 * @param groupBy	分组条件
	 * @return
	 */
	public List<ParamsDTO> getProjectDTOCountList(ParamsDTO pe ,String groupBy){
		StringBuffer sb = new StringBuffer();
		sb.append( " SELECT COUNT(*) AS COUNT, r.`SystemId` AS systemId, s.`SystemName` AS systemName,r.`RecordJudge` as recordJudge ");
		sb.append( " ,p.`CommissionCorpName` AS commissionCorpName ");
		sb.append( " ,r.`ObjType1` as objType1 ");
		sb.append( " FROM t_record AS r LEFT JOIN t_system AS s ON r.`SystemId` = s.`SystemId` LEFT JOIN t_project AS p ON s.`ProjectID` = p.`ProjectID` LEFT JOIN t_test AS t ON r.`TestItemCode` = t.`TestItemCode` ");
		sb.append(this.getWhereSql(pe));
		sb.append( " GROUP BY "+groupBy);
		String sql = sb.toString();
		List<ParamsDTO> list = this.getList(sql,ParamsDTO.class);
		return list;
	}
	
}
